<?php
class HinhanhdbsController extends AppController {

	var $name = 'Hinhanhdbs';
    	var $helpers=array('Html','Form','Js','Others');
	var $uses = array('Hinhanhdb','Thongtindb','Kenh','Donvi','Phankenh','Chamsockenh','Thitruongkenh','Zdv');
	var $components = array('RequestHandler');
	function index($kenh_id=null)
	{
		if(!$kenh_id)
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		$this->Hinhanhdb->recursive = 0;
		$this->paginate = array('conditions'=>array('kenh_id'=>$kenh_id),'order'=>array('kenh_id','ngaytao'));
		$this->set('hinhanhdbs', $this->paginate());
		$ttin = $this->Hinhanhdb->find('list',array('recursive'=>0,'conditions'=>array('Hinhanhdb.kenh_id'=>$kenh_id),
													'fields'=>array('Thongtindb.id','Thongtindb.tenthuoctinh')));
        $kenhs=$this->Hinhanhdb->query('select * from kenhs where id='.$kenh_id);
        $thongtindbs = $this->Thongtindb->find('list',array('conditions'=>array('sudung'=>1),'fields'=>array('tenthuoctinh')));
        
		$this->set(compact('kenh_id','kenhs','thongtindbs','ttin'));
	}

	function view($id = null)
	{
		if (!$id) {
			$this->Session->setFlash(__('Invalid hinhanhdb', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set('hinhanhdb', $this->Hinhanhdb->read(null, $id));
	}

	function add($kenh_id=null)
	{
		if (!empty($this->data))
		{
			$this->Hinhanhdb->create();
			$thongtins = $this->data['Hinhanhdb']['thongtin'];
			foreach ($thongtins as $keytt => $valuett)
			{
				$record['Hinhanhdb']['kenh_id']=$this->data['Hinhanhdb']['kenh_id'];
				$record['Hinhanhdb']['thongtindb_id']=$keytt;
				$record['Hinhanhdb']['noidung']=$valuett;
				$record['Hinhanhdb']['ngaytao']=$this->data['Hinhanhdb']['ngaytao'];
				if(!$this->Hinhanhdb->saveAll($record))
				{
					$this->Session->setFlash(__('Thông tin không thể cập nhật. Vui lòng thử lại', true));
				}				
			}
			$this->Session->setFlash(__('Thông tin đã cập nhật', true));
			$this->redirect(array('action' => 'index',$kenh_id));
		}
		$kenhs = $this->Hinhanhdb->Kenh->find('list');
		$thongtindbs = $this->Hinhanhdb->Thongtindb->find('list');
		$this->set(compact('kenhs', 'thongtindbs'));
	}

	function edit($id = null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid hinhanhdb', true));
			$this->redirect(array('action' => 'index'));
		}
		if (!empty($this->data)) {
			if ($this->Hinhanhdb->save($this->data)) {
				$this->Session->setFlash(__('The hinhanhdb has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The hinhanhdb could not be saved. Please, try again.', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Hinhanhdb->read(null, $id);
		}
		$kenhs = $this->Hinhanhdb->Kenh->find('list');
		$thongtindbs = $this->Hinhanhdb->Thongtindb->find('list');
		$this->set(compact('kenhs', 'thongtindbs'));
	}

	function delete($id = null,$kenh_id=null) {
		if (!$id)
		{
			$this->Session->setFlash(__('Thông tin không tồn tại', true));
			$this->redirect(array('action'=>'index',$kenh_id));
		}
		$hinhanh_id = split('_',$id);
		if($this->Hinhanhdb->deleteAll(array('Hinhanhdb.id'=>$hinhanh_id)))
		{
			$this->Session->setFlash(__('Đã xóa thông tin', true));
			$this->redirect(array('action'=>'index',$kenh_id));
		}
		$this->Session->setFlash(__('Không thể xóa. vui lòng thử lại', true));
		$this->redirect(array('action'=>'index',$kenh_id));
	}

	function update_noidung(){
        if ($this->data) {
            App::import('Core', 'sanitize');
            $title = Sanitize::clean($this->data['Hinhanhdb']['hinhanh']);
            $this->Hinhanhdb->id = $this->data['Hinhanhdb']['id'];
            $this->Hinhanhdb->saveField('noidung', $title);
            $this->set('posttitle', $title);
        }
    }

    function theodoidb($donvi_id=null,$nhanvien_id=null)
    {
    	
    	$str = substr($this->Session->read('cur_tendv'),0,2);
    	if($str!='TT')
    	{
    		$donvi_cha = $this->Session->read('cur_cha');
    		$donvis = $this->Thuocdv->find('list',array(
    										'recursive'=>0,
    										'conditions'=>array('Thuocdv.donvi_id'=>$donvi_cha,'left(Donvi.madv,2)'=>'TT'),
    										'fields'=>array('Donvi.id','Donvi.tendv')));
    		$donvi_id = array_keys($donvis);
    	}
    	else
    	{
    		$donvis = $this->Donvi->find('list',array('conditions'=>array('id'=>$donvi_id),
    													'fields'=>array('id','tendv')));
    	}
    	if(!$nhanvien_id)
    	{
    		$nhanviens = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
    															'fields'=>array('id','tennv')));
    		$nhanvien_id = array_keys($nhanviens);
    	}
    	
    	$kenhs = $this->Phankenh->find('list',array(
    												'recursive'=>0,
    												'conditions'=>array('nhanvien_id'=>$nhanvien_id,'ngaykt_hd'=>null,'Kenh.loaikenh_id'=>2),
    												'fields'=>array('Kenh.id','Kenh.tenkenh')));
    	$nams = $this->Chamsockenh->find('list',array('fields'=>array('year(Chamsockenh.ngaydi)'),
    													'group'=>array('year(Chamsockenh.ngaydi)')));
    	$nams = array(date('Y')=>date('Y'));
    	
    	$this->set(compact('donvis','kenhs','nhanviens','nams'));
    }

	function laykq()
    {
    	
        $this->Cookie->write('cdonvi_id',$this->data['Hinhanhdb']['donvi_id'],100);
    	$this->Cookie->write('cnam',$this->data['Hinhanhdb']['nam'],100);
    	$this->Cookie->write('cthang',$this->data['Hinhanhdb']['thang'],100);
    	
    	
    	
        
        $this->layout = 'ajax';
    	$donvi_id = $this->data['Hinhanhdb']['donvi_id'];
        
        
    	$nam =$this->data['Hinhanhdb']['nam'];
        
    	$thang =$this->data['Hinhanhdb']['thang'];
    	$cuoithang = $this->endofmonth($thang,$nam);
    	$dauthang = date('Y-m-d',strtotime("{$nam}-{$thang}-01"));
    	$donvi_id = $this->data['Hinhanhdb']['donvi_id'];
    	
    	if(isset($this->data['Hinhanhdb']['kenh'])){
    	    
    		$kenh_id = $this->data['Hinhanhdb']['kenh'];
            $this->Cookie->write('ckenhc',$this->data['Hinhanhdb']['kenh'],100);
        }
    	else{
    	   
    		$kenh_id = $this->data['kenh'];
            $this->Cookie->write('ckenh',$this->data['kenh'],100);
   		}
    		
    	if(empty($kenh_id))
    	{
	    	if(isset($this->data['Hinhanhdb']['nhanvien_id']))
	    	{
	    		if(empty($this->data['Hinhanhdb']['nhanvien_id']))
	    		{
	    			if(empty($donvi_id))
					{
						$donvi_cha = $this->Session->read('cur_cha');
						$donvi_id = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_cha)));
					}
				$nhanvien_id = $this->Donvi->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																		'fields'=>array('id','id')));
	    		}
	    		else
	    		{
	    			$nhanvien_id = $this->data['Hinhanhdb']['nhanvien_id'];
                    $this->Cookie->write('cnhanvien_idc',$this->data['Hinhanhdb']['nhanvien_id'],100);
	   	
	    		}
	    	}
	    	else
	    	{
	    		if(empty($this->data['nhanvien_id']))
	    		{
	    			if(empty($donvi_id))
					{
						$donvi_cha = $this->Session->read('cur_cha');
						$donvi_id = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_cha)));
					}
				$nhanvien_id = $this->Donvi->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																		'fields'=>array('id','id')));
	    		}
	    		else
	    		{
	    			$nhanvien_id = $this->data['nhanvien_id'];
                    
       	            $this->Cookie->write('cnhanvien',$this->data['nhanvien_id'],100);
        
	    		}
	    	}
	    	$kenh_id= $this->Phankenh->find('list',array(
														'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																			'ngaykt_hd'=>null),
														'fields'=>array('kenh_id','kenh_id')));
    	}
    	
		$this->Phankenh->unbindModel(array('hasMany'=>array('Kqbanhang')));
		$this->Nhanvien->unbindModel(array('belongsTo'=>array('Phankenh','Chucdanh'),'hasMany'=>array('Chitieunv','Phankenh','Nvchithi','Nvngay','Phankenh','Tempnhiemvunv','User')));
    	$this->Hinhanhdb->Kenh->unbindModel(array('belongsTo'=>array('Loaikenh','Donvi'),'hasMany'=>array('Phankenh')));//
    	
    	$kenhinfo = $this->Phankenh->find('all',array('recursive'=>2,'conditions'=>array('Kenh.id'=>$kenh_id),'order'=>array('Phankenh.nhanvien_id')));
    	
    	
    	$qstrcongtacdbs = "SELECT * 
							FROM Hinhanhdbs Hinhanhdb
							WHERE DATE_FORMAT( Hinhanhdb.ngaytao,  '%Y-%m-%d' ) >=  '".$dauthang."'
									AND DATE_FORMAT( Hinhanhdb.ngaytao,  '%Y-%m-%d' ) <=  '".$cuoithang."'
									AND kenh_id IN (".implode(",",$kenh_id).") 
									AND Hinhanhdb.ngaytao
											IN (SELECT MAX( ctksub.ngaytao ) 
												FROM Hinhanhdbs ctksub
												WHERE ctksub.kenh_id = Hinhanhdb.kenh_id
												AND DATE_FORMAT( ctksub.ngaytao,  '%Y-%m-%d' ) >=  '".$dauthang."'
												AND DATE_FORMAT( ctksub.ngaytao,  '%Y-%m-%d' ) <=  '".$cuoithang."')";
 		
    	$hinhanhdbs = $this->Hinhanhdb->query($qstrcongtacdbs);
    	
    	/*$thongtintts = $this->Thitruongkenh->find('all',array('conditions'=>array('DATE_FORMAT( Thitruongkenh.ngaytao,"%Y-%m-%d") >='=>$dauthang,
    																'DATE_FORMAT( Thitruongkenh.ngaytao,"%Y-%m-%d") <='=>$cuoithang),
    											'fields'=>array('Kenh_id',"GROUP_CONCAT(Thitruongkenh.thitruong SEPARATOR '<br> -') as thitruong" )));*/
    	
    	$thongtintts = $this->Thitruongkenh->find('all',array('conditions'=>array('DATE_FORMAT( Thitruongkenh.ngaytao,"%Y-%m-%d") >='=>$dauthang,
    																'DATE_FORMAT( Thitruongkenh.ngaytao,"%Y-%m-%d") <='=>$cuoithang,
    																'kenh_id'=>$kenh_id),
    											'fields'=>array('kenh_id',"GROUP_CONCAT(Thitruongkenh.thitruong SEPARATOR '<br> -') as thitruong" ),
    											'group'=>array('kenh_id')));
    	
    	$chamsocs = $this->Chamsockenh->find('all',array('conditions'=>array('kenh_id'=>$kenh_id,'DATE_FORMAT(Chamsockenh.ngaydi,"%Y-%m-%d") >='=>$dauthang , 'DATE_FORMAT(Chamsockenh.ngaydi,"%Y-%m-%d") <='=>$cuoithang ),
    													'fields'=>array('kenh_id','Chamsockenh.ngaydi','count(Chamsockenh.ngaydi) as solan'),
    													'group'=>array('kenh_id','DATE_FORMAT(Chamsockenh.ngaydi,"%Y-%m-%d")')));//,'DATE_FORMAT(Chamsockenh.ngaydi,"%Y-%m-%d")'
    	$this->Thongtindb->displayField='tenthuoctinh';
    	$thuoctinhs = $this->Thongtindb->find('list',array('conditions'=>array('sudung'=>1)));
    	$this->set(compact('kenhinfo','hinhanhdbs','thongtintts','chamsocs','dauthang','cuoithang'));
        $this->set(compact('donvi_id','nam','thang','thuoctinhs'));
    
    	
    }

    function laykqe()
    {
    	
    	
        
        $this->layout = '';
    	$donvi_id = $this->data['Hinhanhdb']['donvi_id'];
        
        
    	$nam =$this->data['Hinhanhdb']['nam'];
        
    	$thang =$this->data['Hinhanhdb']['thang'];
    	$cuoithang = $this->endofmonth($thang,$nam);
    	$dauthang = date('Y-m-d',strtotime("{$nam}-{$thang}-01"));
    	$donvi_id = $this->data['Hinhanhdb']['donvi_id'];
    	
    	if(isset($this->data['Hinhanhdb']['kenh'])){
    	    
    		$kenh_id = $this->data['Hinhanhdb']['kenh'];
            //$this->Cookie->write('ckenhc',$this->data['Hinhanhdb']['kenh'],100);
        }
    	else{
    	   
    		$kenh_id = $this->data['kenh'];
            //$this->Cookie->write('ckenh',$this->data['kenh'],100);
   		}
    		
    	if(empty($kenh_id))
    	{
	    	if(isset($this->data['Hinhanhdb']['nhanvien_id']))
	    	{
	    		if(empty($this->data['Hinhanhdb']['nhanvien_id']))
	    		{
	    			if(empty($donvi_id))
					{
						$donvi_cha = $this->Session->read('cur_cha');
						$donvi_id = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_cha)));
					}
				$nhanvien_id = $this->Donvi->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																		'fields'=>array('id','id')));
	    		}
	    		else
	    		{
	    			$nhanvien_id = $this->data['Hinhanhdb']['nhanvien_id'];
              //      $this->Cookie->write('cnhanvien_idc',$this->data['Hinhanhdb']['nhanvien_id'],100);
	   	
	    		}
	    	}
	    	else
	    	{
	    		if(empty($this->data['nhanvien_id']))
	    		{
	    			if(empty($donvi_id))
					{
						$donvi_cha = $this->Session->read('cur_cha');
						$donvi_id = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_cha)));
					}
				$nhanvien_id = $this->Donvi->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																		'fields'=>array('id','id')));
	    		}
	    		else
	    		{
	    			$nhanvien_id = $this->data['nhanvien_id'];
                    
       	        //    $this->Cookie->write('cnhanvien',$this->data['nhanvien_id'],100);
        
	    		}
	    	}
	    	$kenh_id= $this->Phankenh->find('list',array(
														'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																			'ngaykt_hd'=>null),
														'fields'=>array('kenh_id','kenh_id')));
    	}
    	
		$this->Phankenh->unbindModel(array('hasMany'=>array('Kqbanhang')));
		$this->Nhanvien->unbindModel(array('belongsTo'=>array('Phankenh','Chucdanh'),'hasMany'=>array('Chitieunv','Phankenh','Nvchithi','Nvngay','Phankenh','Tempnhiemvunv','User')));
    	$this->Hinhanhdb->Kenh->unbindModel(array('belongsTo'=>array('Loaikenh','Donvi'),'hasMany'=>array('Phankenh')));//
    	
    	$kenhinfo = $this->Phankenh->find('all',array('recursive'=>2,'conditions'=>array('Kenh.id'=>$kenh_id),'order'=>array('Phankenh.nhanvien_id')));
    	
    	
    	$qstrcongtacdbs = "SELECT * 
							FROM Hinhanhdbs Hinhanhdb
							WHERE DATE_FORMAT( Hinhanhdb.ngaytao,  '%Y-%m-%d' ) >=  '".$dauthang."'
									AND DATE_FORMAT( Hinhanhdb.ngaytao,  '%Y-%m-%d' ) <=  '".$cuoithang."'
									AND kenh_id IN (".implode(",",$kenh_id).") 
									AND Hinhanhdb.ngaytao
											IN (SELECT MAX( ctksub.ngaytao ) 
												FROM Hinhanhdbs ctksub
												WHERE ctksub.kenh_id = Hinhanhdb.kenh_id
												AND DATE_FORMAT( ctksub.ngaytao,  '%Y-%m-%d' ) >=  '".$dauthang."'
												AND DATE_FORMAT( ctksub.ngaytao,  '%Y-%m-%d' ) <=  '".$cuoithang."')";
 		
    	$hinhanhdbs = $this->Hinhanhdb->query($qstrcongtacdbs);
    	
    	/*
		$thongtintts = $this->Thitruongkenh->find('all',array('conditions'=>array('DATE_FORMAT( Thitruongkenh.ngaytao,"%Y-%m-%d") >='=>$dauthang,
    																'DATE_FORMAT( Thitruongkenh.ngaytao,"%Y-%m-%d") <='=>$cuoithang),
    											'fields'=>array('Kenh_id',"GROUP_CONCAT(Thitruongkenh.thitruong SEPARATOR '<br> - ') as thitruong" )));
    	*/
		$thongtintts = $this->Thitruongkenh->find('all',array('conditions'=>array('DATE_FORMAT( Thitruongkenh.ngaytao,"%Y-%m-%d") >='=>$dauthang,
    																'DATE_FORMAT( Thitruongkenh.ngaytao,"%Y-%m-%d") <='=>$cuoithang,
    																'kenh_id'=>$kenh_id),
    											'fields'=>array('kenh_id',"GROUP_CONCAT(Thitruongkenh.thitruong SEPARATOR '<br> -') as thitruong" ),
    											'group'=>array('kenh_id')));
    	$chamsocs = $this->Chamsockenh->find('all',array('conditions'=>array('kenh_id'=>$kenh_id,'DATE_FORMAT(Chamsockenh.ngaydi,"%Y-%m-%d") >='=>$dauthang , 'DATE_FORMAT(Chamsockenh.ngaydi,"%Y-%m-%d") <='=>$cuoithang ),
    													'fields'=>array('kenh_id','Chamsockenh.ngaydi','count(Chamsockenh.ngaydi) as solan'),
    													'group'=>array('kenh_id','DATE_FORMAT(Chamsockenh.ngaydi,"%Y-%m-%d")')));//,'DATE_FORMAT(Chamsockenh.ngaydi,"%Y-%m-%d")'
    	$this->Thongtindb->displayField='tenthuoctinh';
    	$thuoctinhs = $this->Thongtindb->find('list',array('conditions'=>array('sudung'=>1)));
    	$this->set(compact('kenhinfo','hinhanhdbs','thongtintts','chamsocs','dauthang','cuoithang'));
        $this->set(compact('donvi_id','nam','thang','thuoctinhs'));
    
    	
    }

    function laykqexcel()
    {
    	
        $tendv=$this->Zdv->find('first',array(
            'conditions'=>array('donvi_id'=> $this->data['Congtackenh']['donvi_id']),
            'fields'=>array('tendvcha','tendv')));
        $this->set(compact('tendv'));
        
        $this->data['Hinhanhdb']['donvi_id']=$this->Cookie->read('cdonvi_id');
    	$this->data['Hinhanhdb']['nam']=$this->Cookie->read('cnam');
    	$this->data['Hinhanhdb']['thang']=$this->Cookie->read('cthang');
    	
    	$this->data['Hinhanhdb']['kenh']=$this->Cookie->read('ckenhc');
    	$this->data['kenh']=$this->Cookie->read('ckenh');
       	$this->data['Hinhanhdb']['nhanvien_id']=$this->Cookie->read('cnhanvien_idc');
	   	$this->data['nhanvien_id']=$this->Cookie->read('cnhanvien');
    	$this->laykqe();
    }


	function endofmonth($month=null, $year =null)
	{
	   if (!$month)
	   {
	      $month = date('m');
	   }
	   if (!$year) {
	      $year = date('Y');
	   }
	   $result = strtotime("{$year}-{$month}-01");
	   $result = strtotime('-1 second', strtotime('+1 month', $result));
	   return date('Y-m-d', $result);
	}

	function laynv()
	{
		$this->layout = 'ajax';
		
		$donvi_id = $this->data['Hinhanhdb']['donvi_id'];
		if($donvi_id=='')
		{
			$donvi_cha = $this->Session->read('cur_cha');
			$donvi_id = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_cha),'fields'=>array('donvi','donvi')));
		}
		$nhanvien = $this->Donvi->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																	'fields'=>array('id','tennv')));
		$this->set('nhanvien',$nhanvien);		
	}
	
	function laykenh()
	{
		$this->layout = 'ajax';
		
		$donvi_id = $this->data['Hinhanhdb']['donvi_id'];
	if($donvi_id=='')
		{
			$donvi_cha = $this->Session->read('cur_cha');
			$donvi_id = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_cha),'fields'=>array('donvi','donvi')));
		}
		if(isset($this->data['Hinhanhdb']['nhanvien_id']))
		{
			if(!empty($this->data['Hinhanhdb']['nhanvien_id']))
			{
				$nhanvien_id = $this->data['Hinhanhdb']['nhanvien_id'];
			}
			else
			{$nhanvien_id = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));}

		}else{
			if(!empty($this->data['nhanvien_id']))
			{
				$nhanvien_id = $this->data['nhanvien_id'];
			}
			else
			{$nhanvien_id = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));}
		}
		$kenhs = $this->Phankenh->find('list',array(
														'recursive'=>0,
														'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																			'ngaykt_hd'=>null,
																			'Kenh.loaikenh_id'=>2),
														'fields'=>array('Kenh.id','Kenh.tenkenh')));
		$this->set(compact('kenhs'));
	}

}
